One to Many SQL Query

Question

I am new to sql. Have been programing for a while but have always found a way not to use databases. I know stupid.

I have a questions about One to Many sql queries.

I am building a website so i can understand databases a little bit more. So what i have is one database with 2 tables. One holds the user information and the holds the the hardware for that user. There an be only one user but there can be multiple pieces of hardware for that user. I can write the query to display the user information no problem. What i can't seem to get my head around is how to write the query to get relate all the hardware to that user and display it.

here is my example of tables

user table

user_id  ------ primary key

first_name

last_name

hardware table

hardware_id ---- primary key

serial

model

type

user_id ---- foreign key (link to the user table)

example

user table

user_id      first_name       last_name

01             John             Doe

02             Jane             Doe



hardware table

hardware_id        serial        model        type             user_id

01                  cac21        Dell         Desktop          01

02                  cac22        HP           Laptop           01

03                  cac24        Apple        Laptop           02

Any help would be great.

Thanks GG

I have researched JOINS but i thought it would display the user information more than once. So let say someone searches for John. I want it to display his user information which is fine but i always want it to display all the hardware associated with john as well — GrumpyGeek, Dec 04 '13 at 14:09
The following join would give you only records where they exist in both tables: `FROM user AS u INNER JOIN hardware AS h ON u.user_id = h.user_id`. If that is not the join you are after, this [***graphic***](http://stackoverflow.com/a/17692215/1253219) illustrates what joins do what. — Linger, Dec 04 '13 at 14:12
Beside join you can also use WHERE to filter the rows you want. E.g. if you first fetch the user John from the users_table you can do a query in the hardware table where you ask to find rows where John's ID is used. — olovholm, Dec 04 '13 at 14:15

score 1 · Answer 1 · answered Dec 04 '13 at 14:21

Querying the data from the database will repeat the person's name on each record, but it will be up to you during the generating of your page to have the output only show the name once, then all the hardware for that person, then move on to the next person and all their hardware.

So, using php, you'll have a nested loop. The outer loop to keep track of each new person, the inner loop to show records only for that person. Retain the "ID" of the person to prevent having more than one "John Doe" person who are actually different people. The ID will ensure proper single person.

Now, the query. Joins are showing the relationship between the tables, and all queries that ever reference more than one table should always be done with tableAlias.columnName to prevent any ambiguity. Table name aliases are also a good way to shorten readability of queries.

select
      u.user_id,
      u.first_name,
      u.last_name,
      h.hardware_id,
      h.serial,
      h.model,
      h.type
   from
      user u   <-- the "u" is the alias to user table...
         JOIN hardware h    <-- "h" is the alias for hardware table
            on u.user_id = h.user_id
   order by
      u.first_name,
      u.last_name,
      u.user_id

Notice I'm alphabetizing the list by last name, first name, but then also including the user_id, so if any multiple "John Doe" users, it will be all for same John Doe #1 vs John Doe #2 as a separate block of records.

In your PHP (since I don't do PHP, this is pseudo code)

Get Your Result Set

nCurrentUserID = 0  (to prepare before the loop)

start loop with first record
   if nCurrentUserID is different than current record "User ID"
      start new output line and include the name in output as needed
      nCurrentUserID = current record "user ID"
   end if 

   while on the same record, now you can output the hardware
   fields to your output list

   advance to the next record in the result set
end the loop of all records

be sure to close any html-based table/row/detail tags

I am really new at sql.. is that select statement used to get both the user data or just the hardware data for the user — GrumpyGeek, Dec 04 '13 at 14:42

score 0 · Answer 2 · answered Dec 04 '13 at 14:14

0

try this

SELECT users.firstname,
    users.lastname,
    hardware.serial       
    hardware.model        
    hardware.type FROM users LEFT JOIN hardware ON users.user_id=hardware.user_id

answered Dec 04 '13 at 14:14

AdRock

2,931
9
61
102

One to Many SQL Query

2 Answers2