Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2005
    Posts
    2

    Unanswered: Multiple table select

    Hello,

    Having 6 tables:

    user with col user
    tbl1 with col id, user
    tbl2 with col user, sin, pic
    tbl3 with col user, sin, link
    tbl4 with col user, sin, ref
    tbl5 with col user, sin, lit

    tbl2-5.sin corresponds to tbl1.id

    tbl1-5.user corresponds to user.user

    I want to select:

    id, user, pic, link, ref, lit

    Where

    id is the unique value of tbl1.id

    user is tbl1.user

    pic is the number of rows where tbl2.sin=tbl1.id AND tbl2.user=user.user

    link is the number of rows where tbl3.sin=tbl1.id AND tbl3.user=user.user

    ref is the number of rows where tbl4.sin=tbl1.id AND tbl4.user=user.user

    lit is the number of rows where tbl5.sin=tbl1.id AND tbl5.user=user.user

    With "WHERE" I would like to restrict the output to a specific user e.g. "WHERE user.user='5'"

    I have tried with LEFT JOIN, but not succeeded.

    I would appreciate any suggestion or reference to a thread where a similar query has been discussed. Thanx.

  2. #2
    Join Date
    Aug 2005
    Location
    Oklahoma
    Posts
    9
    good question. i am not sure how to add a COUNT(*) and get other information to one query. can you not put this into multiple queries?

  3. #3
    Join Date
    Dec 2005
    Posts
    2
    I have solved the problem with subselect. It works, but I think there must be a better way. For anyone interested here a copy from the actual code:

    SELECT
    h.id,
    h.taxgen,
    h.taxspe,
    h.user AS data,
    (SELECT COUNT(*) FROM pictures p WHERE h.id = p.sin AND p.user='$usid') AS pics,
    (SELECT COUNT(*) FROM links l WHERE h.id = l.reltosp AND l.user='$usid') AS links,
    (SELECT COUNT(*) FROM literature lit WHERE h.id = lit.sin AND lit.user='$usid') AS lit,
    (SELECT COUNT(*) FROM botxt b WHERE h.id = b.sin AND b.user='$usid') AS txt
    FROM herbarium h
    HAVING data=1 OR pics>0 OR links>0 OR lit>0 OR txt>0
    ORDER BY h.taxgen, h.taxspe

    Thank you cornercuttin, multiple querries would be also a solution, but would require even more complex coding (php+mysql).
    Last edited by oenchen; 12-22-05 at 09:37.

Posting Permissions

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