Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 241
  • Last Modified:

SQL Query Help

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
Thomas Grassi
Asked:
Thomas Grassi
  • 10
  • 6
1 Solution
 
PortletPaulCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
PortletPaulCommented:
:) 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
PortletPaulCommented:
? "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
 
PortletPaulCommented:
Ooops, sorry

FROM dbo.Recordings AS Recordings
      INNER JOIN dbo.Artists AS artists
                  ON Recordings.ArtistID= artists.ArtistID
0
 
PortletPaulCommented:
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
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
PortletPaulCommented:
now you have gone to an extreme...

SELECT  (nothing)
FROM ....

It;s expecting you to select at least one column :)
0
 
Thomas GrassiSystems AdministratorAuthor Commented:
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
 
PortletPaulCommented:
>>"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
 
Thomas GrassiSystems AdministratorAuthor Commented:
Paul

Thanks for the help

Now on to my ASP Code,
0
 
PortletPaulCommented:
Thanks, good luck on that coding. Paul
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now