Results 1 to 5 of 5
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    One to many query (2K3)

    <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> moment

    I have two tables tblSuppliers and tblProducts ( 1 to Many in place)

    <pre>
    tblSuppliers

    SupplierID CompName
    1 A
    2 B
    3 C


    tblProducts

    ProductID SupplierID Colour
    1 1 Red
    2 1 Red
    3 1 White
    4 2 Red
    5 3 White

    </pre>


    I am going mad trying to get the SQL to show the count of the companies which sell red widgets...can you help please?
    Jerry

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One to many query (2K3)

    One possibility is to use two queries:

    1) Create a query qrySellers based on tblProducts that selects unique SupplierIDs that sell a red product:

    SELECT DISTINCT SupplierID FROM tblProducts WHERE Colour = "Red"

    2) Create a query based on qrySellers that returns the count:

    SELECT Count(*) AS TheCount FROM qrySellers

    Another possibility is to use MarkD's CountUniqueRecords function from <post:=205,252>post 205,252</post:>. In my reply to that post, I extended the function to take a where-condition argument.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: One to many query (2K3)

    Thanks Hans

    That is how I was doing it with two queries so I don't feel so bad now <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    I was just thinking that there was more concise way in one query, thanks
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: One to many query (2K3)

    Well, actually you can with a subquery:

    SELECT Count(*) AS TheCount FROM (SELECT DISTINCT SupplierID FROM tblProducts WHERE Colour = "Red")

    But you still need to design the separate queries first to create this, unless you are fluent in SQL.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> I originally thought that the <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15> in the first post meant that you had had One too many <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: One to many query (2K3)

    Right on the button

    <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15> I had put the distinct outside the subquery, lateness of hour I'm afraid...

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>I have moved on since my first entry to the Lounge and prefer SQL now
    Jerry

Posting Permissions

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