Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Unanswered: Find same records with same ID

    Hello,

    Having trouble describing my problem……

    I have the table below, and I am trying to retrieve TileIDs that have the same ModelIDs.

    ModelID TileID
    HP DL380 G3 120v Dual 15400
    HP DL380 G3 120v Dual 15400
    HP DL380 G3 120v Dual 15400
    HP DL380 G3 120v Dual 15400
    HP DL380 G3 120v Dual 15400
    HP DL380 G3 120v Dual 15400
    Sun SF 280R 120v 15401
    Sun SF 280R 120v 15401
    Sun SF 280R 120v 15401
    Sun SF 280R 120v 15401
    Lantronix MSS4 15401



    So TileID ‘15400’ would be a keeper, since all ModelIDs are the same.

    Any help would be appreciated.

    Thanks

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    SELECT * FROM myTable99 WHERE TILEID IN (
    SELECT TILEID FROM (
    SELECT DISCTINCT MODELID, TILEID
    FROM myTable99) AS XXX
    GROUP BY TILEID
    HAVING COUNT(*) = 1)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    unnecessarily complex, brett

    try this:
    Code:
    select TileID
      from daTable
    group by TileID
    having COUNT(DISTINCT ModelID) = 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Aug 2007
    Posts
    2
    Brett,r937

    Thanks for the fast responses

    I tested both and found that Brett's code is complex for a reason....it worked.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so did mine, i tested it

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •