Solved

SQL Query Help

Posted on 2014-07-23
16
223 Views
Last Modified: 2014-07-24
SQL 2008

Have a database with three tables.

dbo.table1   has 2400 records
dbo.table2   has 5800 records

table1

ID    Name


Table2
ID  Title  Year Location Table1_ID

Need a query to list the data in both tables  my question is how to setup the query so that I get output that look like this

A
This is Record One
This is Record Two
This is Record Three
B
This is Record One
C
This is Record One
This is Record Two
D
This is Record One

etc

thoughts
0
Comment
Question by:Thomas Grassi
  • 10
  • 6
16 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40215850
SQL is not a report generator. What you propose as an output looks very much like a report (something like a table of contents).

SQL produces grids, it is more likely to look like this:

A This is Record One
A This is Record Two
A This is Record Three
B This is Record One
C This is Record One
C This is Record Two
D This is Record One

(the repetition is deliberate! try ordering an output without the As Bs etc repeated)
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40215856
PortletPaul


Yes I know so what would the query be for that ?

I am working on ASP code want to see if I can get the results via my web page but I need the query first
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40215869
:) now I'm aware you are using ASP for the output formatting.

I can produce the query, but you have given me "not much detail", it might be something like this:

select
  t2.ID
, t2.Title
, t2.Year
, t2.Locationm
, t1.Name
from table2 as t2
INNER JOIN table1 as t1 on t2.Table1_ID = t1.ID

OR

select
  t2.ID
, t2.Title
, t2.Year
, t2.Locationm
, t1.Name
from table2 as t2
LEFT OUTER JOIN table1 as t1 on t2.Table1_ID = t1.ID

nb: I don't know where the As Bs ... come from

& I do not know what to suggest for where or order

{+edit}
by the way you mention 3 tables in the question but only list 2.
Is there a third to deal with also?

Using actual table & field names and sample data usually works best overall. An expected result that relates to the sample data is also useful.
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40215905
Ok

Real names now

dbo.artists   is table 1
dbo.recordings is table 2


My attempt

SELECT dbo.Recordings.RecordingID,dbo.Recordings.RecordingTitle, dbo.Artists.ArtistName from dbo.Recordings as Recordings inner join dbo.Artists as artists on dbo.Recordings.RecordingID = dbo.Artists.ArtistID

Open in new window


Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Recordings.RecordingID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Artists.ArtistID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Recordings.RecordingID" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Recordings.RecordingTitle" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.Artists.ArtistName" could not be bound.



The A & B's are the artist names
The record One two and three are recordings

dbo.artists
artistID
artistname

dbo.recordings
recordingID
RecordingTitle
ArtistID
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40215918
If you use aliases, use them throughout, or, don't use aliases. Here are the 2 alternatives:
SELECT
      Recordings.RecordingID
    , Recordings.RecordingTitle
    , artists.ArtistName
FROM dbo.Recordings AS Recordings
      INNER JOIN dbo.Artists AS artists
                  ON Recordings.RecordingID = artists.ArtistID
;

SELECT
      dbo.Recordings.RecordingID
    , dbo.Recordings.RecordingTitle
    , dbo.Artists.ArtistName
FROM dbo.Recordings
      INNER JOIN dbo.Artists
                  ON dbo.Recordings.RecordingID = dbo.Artists.ArtistID
;

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40215922
By the way for As Bs etc you could do something like this:
SELECT
      Recordings.RecordingID
    , Recordings.RecordingTitle
    , artists.ArtistName
    , UPPER (LEFT (artists.ArtistName)) AS alpha1
FROM dbo.Recordings AS Recordings
      INNER JOIN dbo.Artists AS artists
                  ON Recordings.RecordingID = artists.ArtistID
;

Open in new window

you could test what that provides using this:
SELECT DISTINCT
      UPPER (LEFT (ArtistName,1)) AS alpha1
FROM dbo.Artists
;

Open in new window

0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40215925
dbo.artists      first 10 records
1      !!!
2      Til Tuesday
3      100 Proof Aged In Soul
4      10000 Maniacs
5      10CC
6      112
7      1910 Fruitgum Company
8      1998 London Cast Recording
9      2 Pac
10      2 Unlimited

dbo.recordings  records that match the above

1      Louden Up Now           NULL      NULL      NULL      NULL      NULL      NULL      Punk      1
2      Me and Guiliani Down by the Sc      NULL      NULL      NULL      NULL      NULL      NULL      Punk      1
3      Billboard Top 100 - 1985      NULL      NULL      NULL      NULL      NULL      NULL      New Wave      2
4      Billboard Top 100 - 1970      NULL      NULL      NULL      NULL      NULL      NULL      Funk Soul      3
5      Billboard Top 100 - 1994      NULL      NULL      NULL      NULL      NULL      NULL      Alternative Rock      4
6      Billboard Top 100 - 1997      NULL      NULL      NULL      NULL      NULL      NULL      Alternative Rock      4
7      Billboard Top 100 - 1975      NULL      NULL      NULL      NULL      NULL      NULL      Rock      5
8      Billboard Top 100 - 1977      NULL      NULL      NULL      NULL      NULL      NULL      Rock      5
9      112      NULL      NULL      NULL      NULL      NULL      NULL      R&B      6
10      Billboard Top 100 - 1968      NULL      NULL      NULL      NULL      NULL      NULL      POP      7
11      Billboard Top 100 - 1969      NULL      NULL      NULL      NULL      NULL      NULL      POP      7
12      Oklahoma!      NULL      NULL      NULL      NULL      NULL      NULL      Musicals      8
13      Resurrection      NULL      NULL      NULL      NULL      NULL      NULL      RAP      9
14      Strictly 4 My N.I.G.G.A.Z      NULL      NULL      NULL      NULL      NULL      NULL      RAP      9
15      Wanted Dead or Alive      NULL      NULL      NULL      NULL      NULL      NULL      RAP      9
16      All Eyez On Me      NULL      NULL      NULL      NULL      NULL      NULL      RAP      9
18      Greatest Hits      NULL      NULL      NULL      NULL      NULL      NULL      RAP      9
20      Billboard Top 100 - 1995      NULL      NULL      NULL      NULL      NULL      NULL      RAP      10
21      Hits Unlimited (Reissue)      NULL      NULL      NULL      NULL      NULL      NULL      RAP      10


Sample output from your example

RecordingID      RecordingTitle      ArtistName
1      Louden Up Now      !!!
2      Me and Guiliani Down by the Sc      Til Tuesday
3      Billboard Top 100 - 1985      100 Proof Aged In Soul
4      Billboard Top 100 - 1970      10000 Maniacs
5      Billboard Top 100 - 1994      10CC
6      Billboard Top 100 - 1997      112
7      Billboard Top 100 - 1975      1910 Fruitgum Company
8      Billboard Top 100 - 1977      1998 London Cast Recording
9      112      2 Pac
10      Billboard Top 100 - 1968      2 Unlimited



Does not match up and I do not need the recordingId as output  


thoughts
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40215935
? "Does not match up"
yes it does,

10      Billboard Top 100 - 1968     ...      7  joins on  7      1910 Fruitgum Company

the artist "1910 Fruitgum Company" is associated with recording "Billboard Top 100 - 1968"


"I do not need the recordingId as output  "
you may remove it from the select clause
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40215940
Ooops, sorry

FROM dbo.Recordings AS Recordings
      INNER JOIN dbo.Artists AS artists
                  ON Recordings.ArtistID= artists.ArtistID
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40215943
corrected join, like this, note I have assumed a field name of [Genre] in the recordings table
SELECT
      UPPER (LEFT (artists.ArtistName, 1)) AS alpha1
    , artists.ArtistName
    , Recordings.RecordingTitle
    , Recordings.Genre
FROM dbo.Recordings AS Recordings
      INNER JOIN dbo.Artists AS artists
                  ON Recordings.ArtistID = artists.ArtistID
ORDER BY
      artists.ArtistName
    , Recordings.RecordingTitle
;

Open in new window

| ALPHA1 |                 ARTISTNAME |                 RECORDINGTITLE |            GENRE |
|--------|----------------------------|--------------------------------|------------------|
|      ! |                        !!! |                  Louden Up Now |             Punk |
|      ! |                        !!! | Me and Guiliani Down by the Sc |             Punk |
|      1 |     100 Proof Aged In Soul |       Billboard Top 100 - 1970 |        Funk Soul |
|      1 |              10000 Maniacs |       Billboard Top 100 - 1994 | Alternative Rock |
|      1 |              10000 Maniacs |       Billboard Top 100 - 1997 | Alternative Rock |
|      1 |                       10CC |       Billboard Top 100 - 1975 |             Rock |
|      1 |                       10CC |       Billboard Top 100 - 1977 |             Rock |
|      1 |                        112 |                            112 |              R&B |
|      1 |      1910 Fruitgum Company |       Billboard Top 100 - 1968 |              POP |
|      1 |      1910 Fruitgum Company |       Billboard Top 100 - 1969 |              POP |
|      1 | 1998 London Cast Recording |                      Oklahoma! |         Musicals |
|      2 |                      2 Pac |                 All Eyez On Me |              RAP |
|      2 |                      2 Pac |                  Greatest Hits |              RAP |
|      2 |                      2 Pac |                   Resurrection |              RAP |
|      2 |                      2 Pac |      Strictly 4 My N.I.G.G.A.Z |              RAP |
|      2 |                      2 Pac |           Wanted Dead or Alive |              RAP |
|      2 |                2 Unlimited |       Billboard Top 100 - 1995 |              RAP |
|      2 |                2 Unlimited |       Hits Unlimited (Reissue) |              RAP |
|      T |                Til Tuesday |       Billboard Top 100 - 1985 |         New Wave |

Open in new window

http://sqlfiddle.com/#!3/febe1/4
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40215946
Ok

select from dbo.Recordings AS Recordings
       INNER JOIN dbo.Artists AS artists
                   ON Recordings.ArtistID= artists.ArtistID 

Open in new window



Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40215950
now you have gone to an extreme...

SELECT  (nothing)
FROM ....

It;s expecting you to select at least one column :)
0
 
LVL 23

Author Comment

by:Thomas Grassi
ID: 40218223
Paul

Thanks

I modified it to this

SELECT
     artists.ArtistName
    , Recordings.RecordingTitle
FROM dbo.Recordings AS Recordings
      INNER JOIN dbo.Artists AS artists
                  ON Recordings.ArtistID = artists.ArtistID
ORDER BY
      artists.ArtistName
    , Recordings.RecordingTitle

Open in new window


My output looks good like this

ArtistName      RecordingTitle
!!!      Louden Up Now
!!!      Me and Guiliani Down by the Sc
100 Proof Aged In Soul      Billboard Top 100 - 1970
10000 Maniacs      Billboard Top 100 - 1994
10000 Maniacs      Billboard Top 100 - 1997
10CC      Billboard Top 100 - 1975
10CC      Billboard Top 100 - 1977

Can I put the artist name as a one entry like this

ArtistName      RecordingTitle
!!!
!!!      Louden Up Now
!!!      Me and Guiliani Down by the Sc
100 Proof Aged In Soul
100 Proof Aged In Soul      Billboard Top 100 - 1970
10000 Maniacs
10000 Maniacs      Billboard Top 100 - 1994
10000 Maniacs      Billboard Top 100 - 1997
10CC
10CC      Billboard Top 100 - 1975
10CC      Billboard Top 100 - 1977
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40218256
>>"Can I put the artist name as a one entry like this "

please refer to my first comment http:l#a40215850 as you get closer to a "report" your query will get more complex, and you should consider using ASP instead.

but, here is that query.
SELECT
      artists.ArtistName
    , Recordings.RecordingTitle
FROM dbo.Artists AS artists
      INNER JOIN dbo.Recordings AS Recordings
                  ON artists.ArtistID = Recordings.ArtistID
UNION ALL
    SELECT
          artists.ArtistName
        , NULL RecordingTitle
    FROM dbo.Artists AS artists

ORDER BY
      ArtistName
    , RecordingTitle

Open in new window

http://sqlfiddle.com/#!3/febe1/6
0
 
LVL 23

Author Closing Comment

by:Thomas Grassi
ID: 40218334
Paul

Thanks for the help

Now on to my ASP Code,
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40218338
Thanks, good luck on that coding. Paul
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sql server lock cursor 13 41
encyps queries mssql 15 27
Usage Scenarios for Extended Events? 1 12
Update in Sql 7 0
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now